import xlrd
import datetime
import json
import pymysql
import uuid
import decimal
import configparser
from pymysql import IntegrityError, Error, OperationalError


class Test:
    def __init__(self):
        # 连接MySQL数据库
        # self.connect = pymysql.connect(host='124.71.29.162', user='root', password='St1282001316*',
        #                                db='scrapy_hospital',
        #                                port=3306)
        config = configparser.ConfigParser()
        config.read("config.ini")
        host = config.get("dbconfig", "host")
        user = config.get("dbconfig", "user")
        password = config.get("dbconfig", "password")
        db = config.get("dbconfig", "db")
        port = int(config.get("dbconfig", "port"))
        self.createId = int(config.get("dbconfig", "createId"))
        self.updateId = int(config.get("dbconfig", "createId"))
        self.connect = pymysql.connect(host=host, user=user, password=password,
                                       db=db,
                                       port=port)
        self.cursor = self.connect.cursor()
        print("数据库连接成功")

    # def __init__(self):
    #     # 连接MySQL数据库
    #     # self.connect = pymysql.connect(host='124.71.29.162', user='root', password='St1282001316*',
    #     #                                db='scrapy_hospital',
    #     #                                port=3306)
    #     # self.connect = pymysql.connect(host=host, user=user, password=password,
    #     #                                db=db,
    #     #                                port=port)
    #     # self.cursor = self.connect.cursor()
    #     # print("数据库连接成功")
    #     pass
    # file1  药监局文件     file2  爱企查文件
    def readExcel(self, file1, file2, needUpdate):
        wb1 = xlrd.open_workbook(filename=file2)
        sheet1 = wb1.sheet_by_index(0)
        # dataList2为药监局数据    dataList1为爱企查数据
        dataList1 = []
        dataList2 = []
        i = 3
        while i < sheet1.nrows:
            data = {}
            enterpriseName = str(sheet1.row(i)[0].value)
            if not enterpriseName.endswith("公司") or enterpriseName.endswith("分公司"):
                i += 1
                continue
            data["c_enterprise_name"] = enterpriseName
            data["c_legal_person"] = sheet1.row(i)[1].value
            data["f_register_capital"] = sheet1.row(i)[2].value
            data["d_create_date"] = sheet1.row(i)[3].value
            data["c_province"] = sheet1.row(i)[5].value
            data["c_city"] = sheet1.row(i)[6].value
            data["c_area"] = sheet1.row(i)[7].value
            data["c_phone"] = sheet1.row(i)[13].value
            data["c_address"] = sheet1.row(i)[15].value
            data["c_site"] = sheet1.row(i)[16].value
            data["c_manage_range"] = sheet1.row(i)[18].value
            dataList1.append(data)
            i += 1
        # print(data)
        # print("爱企查列表:>>>", dataList1)

        wb2 = xlrd.open_workbook(filename=file1)
        sheet2 = wb2.sheet_by_index(0)
        j = 0
        while j < sheet2.nrows:
            data = {}
            enterpriseName = str(sheet2.row(j)[0].value)
            if enterpriseName.endswith("公司") and not enterpriseName.endswith("分公司"):
                data["c_enterprise_name"] = enterpriseName
                dataList2.append(data)
            j += 1
        # print("药监局列表:>>>>", dataList2)
        dataList3 = []  # 匹配上的结果
        dataList4 = []  # 没有匹配上的结果
        for m in dataList2:
            flag = True
            for n in dataList1:
                if m["c_enterprise_name"] == n["c_enterprise_name"]:
                    dataList3.append(n)
                    flag = False
                    break
            if flag:
                dataList4.append(m)
        print("匹配到的数据")
        # for l in dataList3:
        #     print(l)
        self.dealData(dataList3, dataList4, needUpdate)

    # 生成id
    def generateId(self):
        return str(uuid.uuid1()).replace("-", "")

    def dealData(self, dataList, dataList4, needUpdate):
        for data in dataList:
            data["c_id"] = self.generateId()
            registerCapital = str(data["f_register_capital"]).strip()
            if "万(元)" in registerCapital:
                registerCapital = registerCapital.replace("万(元)", "")
                if "," in registerCapital:
                    # 1,800万(元)
                    registerCapital = registerCapital.replace(",", "")
                data["f_register_capital"] = decimal.Decimal(registerCapital)
                # data["f_register_capital"] = str(data["f_register_capital"]).replace("万(元)", "")
            else:
                data["f_register_capital"] = decimal.Decimal("0.01")
            createDate = str(data["d_create_date"]).strip()[0:10]
            if createDate != "-":
                # print("createDate:>>>>>", createDate)
                if "-" in createDate:
                    # print("createDate:>>>>>", createDate)
                    data["d_create_date"] = datetime.datetime.strptime(createDate, "%Y-%m-%d")
                elif "/" in createDate:
                    data["d_create_date"] = datetime.datetime.strptime(createDate, "%Y/%m/%d")
            else:
                data["d_create_date"] = datetime.datetime.strptime("2222-02-02", "%Y-%m-%d")
                # data["d_create_date"] = None
            province = data["c_province"]
            if len(data["c_city"]) > 2:
                city = str(data["c_city"]).strip()[0:len(str(data["c_city"])) - 1]
            else:
                city = data["c_city"]
            if len(data["c_area"]) > 2:
                area = str(data["c_area"]).strip()[0:len(str(data["c_area"])) - 1]
            else:
                area = data["c_area"]
            data["c_province"] = "-"
            data["c_city"] = "-"
            data["c_area"] = ""
            # 匹配省，市，区(县)
            with open("old_province.json", "r", encoding="UTF-8") as json_object:
                list = json.load(json_object)
                flag = False
                for i in list:
                    if province == i["label"]:
                        data["c_province"] = i["value"]
                        for j in i["children"]:
                            if city in j["label"]:
                                data["c_city"] = j["value"]
                                flag = True
                                if "children" in j.keys():
                                    for k in j["children"]:
                                        if area in k["label"]:
                                            data["c_area"] = k["value"]
                                            break
                            if flag:
                                break
                    if flag:
                        break
            manageRange = str(data["c_manage_range"])
            rangeList = manageRange.split(";")
            for range in rangeList:
                s = ""
                if ("一" in range and "售" in range or "一" in range and "经营" in range) and "1" not in s:
                    s += ",1"
                if ("二" in range and "售" in range or "二" in range and "经营" in range) and "2" not in s:
                    s += ",2"
                if ("三" in range and "售" in range or "三" in range and "经营" in range) and "3" not in s:
                    s += ",3"
                if ("药" in range and "售" in range or "药" in range and "经营" in range) and "4" not in s:
                    s += ",4"
                if s.startswith(","):
                    s = s[1:]
                if s == "":
                    s = "5"
                data["c_sale_type"] = s
                # 设置 创建人 创建时间
            time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            data["dt_create_time"] = time
            data["dt_update_time"] = time
        for i in dataList:
            print("处理过后的数据:>>>>>>>", i)
        self.importData(dataList, dataList4, needUpdate)

    def importData(self, dataList, dataList4, needUpdate):
        # connect = pymysql.connect(host='124.71.29.162', user='root', password='St1282001316*',
        #                           db='scrapy_hospital',
        #                           port=3306)
        # cursor = connect.cursor()
        # print("数据库连接成功")

        insertsql = 'INSERT INTO t_nmd_mp_enterprise_sale (c_id,c_enterprise_name,c_province,c_city,c_area,f_register_capital,d_create_date,' \
                    'c_sale_type,c_legal_person,c_phone, c_site,c_address,c_manage_range,n_create_id, dt_create_time,n_update_id, dt_update_time) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'

        updatesql = 'update t_nmd_mp_enterprise_sale set c_province=%s,c_city=%s,c_area=%s,f_register_capital = %s,d_create_date=%s,' \
                    'c_sale_type=%s,c_legal_person=%s,c_phone=%s,c_site=%s,c_address=%s,c_manage_range=%s,n_update_id=%s, dt_update_time=%s where c_enterprise_name=%s'

        successnum = 0
        failnum = 0
        updatenum = 0
        failList = []
        for data in dataList:
            try:
                self.cursor.execute(insertsql,
                                    (data["c_id"], data["c_enterprise_name"], data['c_province'], data['c_city'],
                                     data['c_area'],
                                     data['f_register_capital'], data['d_create_date'], data['c_sale_type'],
                                     data['c_legal_person'], data['c_phone'], data['c_site'], data['c_address'],
                                     data['c_manage_range'], self.createId, data["dt_create_time"], self.updateId,
                                     data["dt_update_time"]))
                print(data["c_enterprise_name"], "插入成功")
                successnum += 1
                self.connect.commit()
            except IntegrityError:
                try:
                    if needUpdate:
                        self.cursor.execute(updatesql,
                                            (data['c_province'], data['c_city'],
                                             data['c_area'], data['f_register_capital'],
                                             data['d_create_date'], data['c_sale_type'],
                                             data['c_legal_person'], data['c_phone'], data['c_site'], data['c_address'],
                                             data['c_manage_range'], self.updateId,
                                             data["dt_update_time"], data["c_enterprise_name"]))
                        self.connect.commit()
                        print(data["c_enterprise_name"], "更新成功")
                        updatenum += 1
                except IntegrityError:
                    print(data["c_enterprise_name"], "插入失败")
                    failnum += 1
                    fail = {"c_enterprise_name": data["c_enterprise_name"]}
                    failList.append(fail)
                except OperationalError:
                    print(data["c_enterprise_name"], "插入失败")
                    failnum += 1
                    fail = {"c_enterprise_name": data["c_enterprise_name"]}
                    failList.append(fail)

        self.close_db(successnum, updatenum, failnum, failList, dataList4)

    def close_db(self, successnum, updatenum, failnum, failList, dataList4):
        self.cursor.close()
        self.connect.close()
        s = "导入完成,插入{}条,更新{}条,未匹配{}条,失败{}条".format(successnum, updatenum, len(dataList4), failnum)
        now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S").replace("-", "").replace(":", "").replace(" ", "")
        with open('{}.txt'.format(now), 'w') as f:
            f.write(s + '\r')
            f.write('\t【插入失败的数据】\r')
            for l in failList:
                for k, v in l.items():
                    f.write(str(v))
                    f.write('\r')
            f.write('\r\r')
        with open('{}.txt'.format(now), 'a') as f:
            f.write('\t【未匹配到的数据】\r')
            for l in dataList4:
                for k, v in l.items():
                    f.write(str(v))
                    f.write('\r')
        print(s)


# host,user,password,db,port
if __name__ == '__main__':
    file1 = input("输入药监局的文件名")
    file2 = input("输入爱企查的文件名")
    # host = input("输入数据库地址")
    # user = input("输入数据库user")
    # password = input("输入数据库密码")
    # db = input("输入数据库名")
    # port = int(input("输入数据库端口号"))
    # file = "buchong.xls"
    # test = Test(host, user, password, db, port)
    inputstr = input("是否要更新，1-更新，2-不更新")
    needUpdate = False
    if inputstr == "1":
        needUpdate = True
    test = Test()
    test.readExcel(file1, file2, needUpdate)
